package com.lisa.auto.tools.seveneleven;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.alibaba.fastjson.JSONArray;
import com.lisa.auto.entity.seveneleven.po.SevenElevenShipment;
import com.lisa.auto.entity.seveneleven.po.SevenElevenShipmentDto;
import com.lisa.auto.entity.seveneleven.po.SevenElevenShopDto;
import com.lisa.auto.entity.seveneleven.po.SevenElevenVehiclePo;
import com.lisa.auto.util.Cache;
import com.lisa.auto.util.DateUtil;
import com.lisa.auto.util.ExcelStyleUtil;
import com.lisa.auto.util.ExcelUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author huangzhigang 2019/12/24
 */
public class SevenElevenExcelConvertTool {

    static String EXCEL_TITLE_ = "广东赛壹便利店干货装车单 " + DateUtil.format(DateUtil.DATE_1);

    static String TITLE_ = "title";

    static String ENTITY_ = "entity";

    static String DATA_ = "data";

    private static Pattern pattern = Pattern.compile("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");

    private static Cache<SXSSFWorkbook> RESOURCES = new Cache<>();

    public static void readExcel(MultipartFile orderFile, MultipartFile vehicleFile, HttpServletResponse response) throws Exception {
        try {
            /** 解析原始单 */
            List<String> columnName = Arrays.asList(new String[]{"店铺","原箱SP","散货SP","大点章","散货胶箱","笼外胶箱","自动伞(262310)","公主伞(263299)","塑料长柄伞(264531)","格纹伞(264557)","素色伞(264558)","女士伞(264559)","铺型","区域","原箱笼数","计费笼数","原箱箱数"});
            String shipmentStr = ExcelUtils.readExcel(orderFile, SevenElevenComs.ORDER_SHEET_IDX, columnName);
            String resultShipmentr = SevenElevenComs.shipmentDb(shipmentStr);
            List<SevenElevenShipment> shipmentList = JSONArray.parseArray(resultShipmentr, SevenElevenShipment.class);
            /** 解析店铺信息 */
            List<String> columnName1 = Arrays.asList(new String[]{"店铺","线","站","铺型","电话号码","留箱否","送货时间"});
            String shopAddrJson = ExcelUtils.readExcel(orderFile, SevenElevenComs.SHOP_ADDR_SHEET_IDX, columnName1);
            String resultShopAddr = SevenElevenComs.shopAddrDb(shopAddrJson);
            List<SevenElevenShopDto> shopAddrList = JSONArray.parseArray(resultShopAddr, SevenElevenShopDto.class);
            /** 解析车次信息 */
            //List<String> columnName2 = Arrays.asList(new String[]{"企业","客户/品牌名","车次编号","司机","车牌号","司机手机","剩余体积m³","剩余重量kg","司机收入￥","车次状态","取货地址","取货地址编号","取货时间段","外部运单编号","卸货地址","外部店铺编号","卸货地址编号","卸货联系人","卸货联系人电话","卸货时间段","包裹类型","包裹数量","包裹容积","包裹重量","运费","备注"});
            List<String> columnName2 = Arrays.asList(new String[]{"企业","车次编号","司机","车牌号","司机手机","剩余体积m³","剩余重量kg","司机收入费用","车次状态","取货地址","取货地址编号","取货时间段","客户/品牌名","外部运单编号","卸货地址","外部店铺编号","卸货地址编号","卸货联系人","卸货联系人电话","卸货时间段","包裹类型","包裹名称","包裹编号","包裹容积","包裹重量","运费","备注"});
            String vehicleJson = ExcelUtils.readExcel(vehicleFile, "SheetJS", columnName2);
            String resultVehicle = SevenElevenComs.vehicleDb(vehicleJson);
            List<SevenElevenVehiclePo> vehicleList = JSONArray.parseArray(resultVehicle, SevenElevenVehiclePo.class);
            //list根据店铺id去重


            List<String> ids = new ArrayList<>();
            List<SevenElevenVehiclePo> vehicleList1 = vehicleList.stream().filter(
                    v -> {
                        boolean flag = !ids.contains(v.getShopId());
                        ids.add(v.getShopId());
                        return flag;
                    }
            ).collect(Collectors.toList());
            List<SevenElevenShipmentDto> list = new ArrayList<>();
            for (SevenElevenShipment sevenElevenShipment : shipmentList) {
                for (SevenElevenShopDto sevenElevenShopDto : shopAddrList) {
                    if (StringUtils.isBlank(sevenElevenShipment.getShopId()) || StringUtils.isBlank(sevenElevenShopDto.getShopId())){
                        continue;
                    }
                    if (sevenElevenShipment.getShopId().equals(sevenElevenShopDto.getShopId())){
                        for (int i=0; i<vehicleList1.size(); i++) {
                            SevenElevenVehiclePo sevenElevenVehiclePo = vehicleList1.get(i);
                            if (StringUtils.isBlank(sevenElevenVehiclePo.getShopId())){
                                continue;
                            }
                            if (sevenElevenShipment.getShopId().equals(sevenElevenVehiclePo.getShopId())) {
                                SevenElevenShipmentDto sevenElevenShipmentDto = new SevenElevenShipmentDto(i+1,"--","","--");
                                sevenElevenShipmentDto.setShopId(sevenElevenVehiclePo.getShopId());
                                sevenElevenShipmentDto.setStationId(sevenElevenShopDto.getStationId());
                                sevenElevenShipmentDto.setLineId(sevenElevenShopDto.getLineId());
                                sevenElevenShipmentDto.setBulkSP("A"+sevenElevenShipment.getBulkSP());
                                sevenElevenShipmentDto.setOriginalBoxSP("A"+sevenElevenShipment.getOriginalBoxSP());
                                if (pattern.matcher(sevenElevenShipment.getBulkBoxNum()).matches()){
                                    sevenElevenShipmentDto.setBulkBoxNum(Integer.parseInt(sevenElevenShipment.getBulkBoxNum()));
                                }
                                sevenElevenShipmentDto.setColouredUmbrella(Integer.parseInt(sevenElevenShipment.getColouredUmbrella()));
                                sevenElevenShipmentDto.setEstimatedTime(sevenElevenShopDto.getEstimatedTime());
                                if (pattern.matcher(sevenElevenShipment.getGlueBoxNum()).matches()){
                                    sevenElevenShipmentDto.setGlueBoxNum(Integer.parseInt(sevenElevenShipment.getGlueBoxNum()));
                                }
                                if (pattern.matcher(sevenElevenShipment.getHandleUmbrella()).matches()){
                                    sevenElevenShipmentDto.setHandleUmbrella(Integer.parseInt(sevenElevenShipment.getHandleUmbrella()));
                                }
                                sevenElevenShipmentDto.setLeaveBox(sevenElevenShopDto.getleaveBox());
                                if (pattern.matcher(sevenElevenShipment.getLongUmbrella()).matches()){
                                    sevenElevenShipmentDto.setLongUmbrella(Integer.parseInt(sevenElevenShipment.getLongUmbrella()));
                                }
                                if (pattern.matcher(sevenElevenShipment.getOriginalBoxNum()).matches()){

                                    sevenElevenShipmentDto.setOriginalBoxNum(Integer.parseInt(sevenElevenShipment.getOriginalBoxNum()));
                                }
                                if (pattern.matcher(sevenElevenShipment.getOriginalLuggageNum()).matches()){
                                    int ceil = (int)Math.ceil(Double.parseDouble(sevenElevenShipment.getOriginalLuggageNum()));
                                    sevenElevenShipmentDto.setOriginalLuggageNum(ceil);
                                }
                                if (pattern.matcher(sevenElevenShipment.getPlaidUmbrella()).matches()){

                                    sevenElevenShipmentDto.setPlaidUmbrella(Integer.parseInt(sevenElevenShipment.getPlaidUmbrella()));
                                }
                                if (pattern.matcher(sevenElevenShipment.getPriceCageNum()).matches()){
                                    DecimalFormat df = new DecimalFormat("#.00");
                                    String str = df.format(Double.parseDouble(sevenElevenShipment.getPriceCageNum()));
                                    sevenElevenShipmentDto.setPriceCageNum(Double.parseDouble(str));
                                }
                                if (pattern.matcher(sevenElevenShipment.getPrincessUmbrella()).matches()){

                                    sevenElevenShipmentDto.setPrincessUmbrella(Integer.parseInt(sevenElevenShipment.getPrincessUmbrella()));
                                }
                                if (pattern.matcher(sevenElevenShipment.getSelfOpeningUmbrella()).matches()){

                                    sevenElevenShipmentDto.setSelfOpeningUmbrella(Integer.parseInt(sevenElevenShipment.getSelfOpeningUmbrella()));
                                }
                                sevenElevenShipmentDto.setShopType1(sevenElevenShipment.getShopType());
                                sevenElevenShipmentDto.setShopType(sevenElevenShopDto.getShopType());
                                sevenElevenShipmentDto.setTel(sevenElevenShopDto.getTel());
                                sevenElevenShipmentDto.setDriverName(sevenElevenVehiclePo.getDriverName());
                                sevenElevenShipmentDto.setPlate(sevenElevenVehiclePo.getPlate());
                                sevenElevenShipmentDto.setRegion(sevenElevenShipment.getRegion());
                                list.add(sevenElevenShipmentDto);
                            }
                        }
                    }
                }
            }
            /** 设置导出的表头信息*/
            String title = "SevenEleven装车单-"+DateUtil.format(DateUtil.DATE_TIME_1)+".xlsx";
            exportExcel(title, response, list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出装车单
     * @param title 标题
     * @param response
     * @param list 集合数据
     */
    public static void exportExcel(String title, HttpServletResponse response, List<SevenElevenShipmentDto> list){
        Map<String, List<SevenElevenShipmentDto>> stringListMap = list.stream().collect(Collectors.groupingBy(SevenElevenShipmentDto::getPlate));
        List<Map<String, Object>> result = new ArrayList<>(stringListMap.keySet().size());
        stringListMap.keySet().stream().forEach(plate->{
            Map<String, Object> sheetMap = new HashMap<>(3);
            ExportParams exportParams = new ExportParams();
            exportParams.setSheetName(plate);
            sheetMap.put(TITLE_, exportParams);
            sheetMap.put(ENTITY_, SevenElevenShipmentDto.class);
            List<SevenElevenShipmentDto> dataList = stringListMap.get(plate);
            Collections.sort(dataList, Comparator.comparing(SevenElevenShipmentDto::getOrderId));
            String driverName = dataList.get(0).getDriverName() + "                ";
            String secondTitle = String.format("车牌：%s      送货线路：%s", plate, driverName);
            exportParams.setTitle(EXCEL_TITLE_);
            exportParams.setSecondTitle(secondTitle);
            exportParams.setStyle(ExcelStyleUtil.class);
            sheetMap.put(DATA_, total(dataList));
            result.add(sheetMap);
        });
        Workbook workbook = ExcelExportUtil.exportExcel(result, ExcelType.HSSF);
        ExcelUtils.download(workbook, title, response);
    }

    /**
     * 装车单合计处理
     * @param dataList 数据集合
     * @return List<SevenElevenShipmentDto>
     */
    public static List<SevenElevenShipmentDto> total(List<SevenElevenShipmentDto> dataList){
        if (CollectionUtils.isEmpty(dataList)){
            return dataList;
        }
        DecimalFormat df = new DecimalFormat("#.00");

        Integer bulkBoxNumSum = 0, glueBoxNumSum = 0, selfOpeningUmbrellaSum = 0, princessUmbrellaSum = 0, longUmbrellaSum = 0;
        Integer plaidUmbrellaSum = 0, colouredUmbrellaSum = 0, handleUmbrellaSum = 0, originalLuggageNumSum = 0, originalBoxNumSum = 0;
        Double priceCageNumSum = 0.0;
        for (SevenElevenShipmentDto shipment : dataList){
            bulkBoxNumSum += shipment.getBulkBoxNum()==null?0:shipment.getBulkBoxNum();
            glueBoxNumSum += shipment.getGlueBoxNum()==null?0:shipment.getGlueBoxNum();
            selfOpeningUmbrellaSum += shipment.getSelfOpeningUmbrella()==null?0:shipment.getSelfOpeningUmbrella();
            princessUmbrellaSum += shipment.getPrincessUmbrella()==null?0:shipment.getPrincessUmbrella();
            longUmbrellaSum += shipment.getLongUmbrella()==null?0:shipment.getLongUmbrella();
            plaidUmbrellaSum += shipment.getPlaidUmbrella()==null?0:shipment.getPlaidUmbrella();
            colouredUmbrellaSum += shipment.getColouredUmbrella()==null?0:shipment.getColouredUmbrella();
            handleUmbrellaSum += shipment.getHandleUmbrella()==null?0:shipment.getHandleUmbrella();
            originalLuggageNumSum += shipment.getOriginalLuggageNum()==null?0:shipment.getOriginalLuggageNum();
            priceCageNumSum += shipment.getPriceCageNum()==null?0:shipment.getPriceCageNum();
            originalBoxNumSum += shipment.getOriginalBoxNum()==null?0:shipment.getOriginalBoxNum();
        }
        SevenElevenShipmentDto shipmentDto = new SevenElevenShipmentDto("合计", bulkBoxNumSum,  glueBoxNumSum, selfOpeningUmbrellaSum, princessUmbrellaSum, longUmbrellaSum,
                plaidUmbrellaSum, colouredUmbrellaSum, handleUmbrellaSum, originalLuggageNumSum, Double.parseDouble(df.format(priceCageNumSum)), originalBoxNumSum);
        dataList.add(shipmentDto);
        return dataList;
    }

}
